/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package br.terminalrodoviario;

import br.Db;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author marcelo
 */
public class Search {
    private City cityFrom;
    private City cityTo;

    public Search(City cityFrom,City cityTo){
        this.cityFrom = cityFrom;
        this.cityTo = cityTo;
    }

    public City getCityFrom() {
        return cityFrom;
    }

    public void setCityFrom(City cityFrom) {
        this.cityFrom = cityFrom;
    }

    public City getCityTo() {
        return cityTo;
    }

    public void setCityTo(City cityTo) {
        this.cityTo = cityTo;
    }

    public ArrayList<RouteTime> getRoutes(){
        String sql = "select r.id as route_id,c2.name as city_from_name, c2.id as city_from_id, c2.state as city_from_state,rt.name as route_type ,";
        sql+=" c3.name as city_to_name,c3.id as city_to_id,c3.state as city_to_state,cp.id as company_id, cp.name as company_name, cp.logo as company_logo from routes r";
        sql+=" left join routes_cities rc on(rc.id_route = r.id)";
        sql+= " left join cities c on(c.id = rc.id_city)";
        sql+=" left join companies cp on(cp.id = r.id_company)";
        sql+=" left join cities c2 on (r.id_city_from = c2.id)";
        sql+=" left join cities c3 on (r.id_city_to = c3.id)";
        sql+=" left join routes_types rt on(rt.id = r.id_route_type)";
        sql+=" where c.id in("+cityFrom.getId()+") and exists (select rc2.id from routes_cities rc2 inner join cities c4 on(c4.id = rc2.id_city) where c4.id in("+cityTo.getId()+") and rc2.id_route = r.id)";
        sql+=" order by r.id asc";
        Db db = new Db();
        ArrayList<Route> routes = new ArrayList<Route>();
        try {
            ResultSet rs = db.executeResultSet(sql);

            while(rs.next()){
                Route rt = new Route();
                rt.setId(rs.getInt("route_id"));
                RouteType rtp = new RouteType();
                rtp.setName(rs.getString("route_type"));
                rt.setRouteType(rtp);
                rt.setTravels(new ArrayList<RouteTravel>());
                rt.setItinerary(new ArrayList<RouteItinerary>());
                Company cp = new Company();
                cp.setId(rs.getInt("company_id"));
                cp.setName(rs.getString("company_name"));
                cp.setLogo(rs.getString("company_logo"));
                rt.setCompany(cp);
                City cFrom = new City(rs.getString("city_from_name"),rs.getString("city_from_state"));
                cFrom.setId(rs.getInt("city_from_id"));
                rt.setFrom(cFrom);
                City cTo = new City(rs.getString("city_to_name"),rs.getString("city_to_state"));
                cTo.setId(rs.getInt("city_to_id"));
                rt.setTo(cTo);
                routes.add(rt);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Search.class.getName()).log(Level.SEVERE, null, ex);
        }
        routes = fillRouteCities(routes,db);
        return fillRouteTime(routes,db);
    }

    private ArrayList<Route> fillRouteCities(ArrayList<Route> lst, Db db){
        if(lst.size() == 0)
            return new ArrayList<Route>();        
        String ids = "";
        for(Route r:lst){
            ids += r.getId() + ",";
        }
        String sql = "select r.id as route,rc.sequence, c.id as city from routes_cities rc";
        sql +=" left join cities c on(c.id = rc.id_city)";
        sql +=" left join routes r on(r.id = rc.id_route)";
        sql +=" where r.id in("+ ids.substring(0,ids.length()-1) +") and c.id in("+cityFrom.getId()+","+cityTo.getId()+")";
        sql +=" order by r.id asc, rc.sequence asc;";

        try {
            ResultSet rs = db.executeResultSet(sql);

            while(rs.next()){
                RouteItinerary rti = new RouteItinerary();
                rti.setSequence(rs.getInt("sequence"));
                City c = new City();
                c.setId(rs.getInt("city"));
                rti.setCity(c);
                rti.setRoute(getRoute(lst,rs.getInt("route")));
                rti.getRoute().getItinerary().add(rti);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Search.class.getName()).log(Level.SEVERE, null, ex);
        }

        return verifyType(lst);
    }

    private ArrayList<Route> verifyType(ArrayList<Route> lst){
        ArrayList<Route> routes = new ArrayList<Route>();
        for(Route r: lst){
            if(r.getItinerary().size() < 2)
                continue;
            RouteItinerary rti = r.getItinerary().get(0);
            RouteItinerary rti2 = r.getItinerary().get(1);
            City c = rti.getCity();
            City c2 = rti2.getCity();
            if(c.getId() == cityFrom.getId() && rti.getSequence() < rti2.getSequence())
                r.setType(1);
            else
                r.setType(2);
            routes.add(r);
        }
        return routes;
    }

    private ArrayList<RouteTime> fillRouteTime(ArrayList<Route> lst, Db db){
        if(lst.size() == 0)
            return new ArrayList<RouteTime>();
        ArrayList<RouteTime> times = new ArrayList<RouteTime>();
        String condicoes = "";
        for(Route r:lst){
            condicoes += "(r01.id = "+r.getId() + " and rtv.id_type="+r.getType()+" and c01.id in("+cityFrom.getId()+","+cityTo.getId()+")) or";
        }
        String sql = "select r01.id as route_id, t.go_time, c01.id as time_city,rtv.id as travel, rtv.id_type from routes_times t";
        sql +=" left join routes_travels rtv on(rtv.id = t.id_travel)";
        sql +=" left join routes r01 on(r01.id = rtv.id_route)";
        sql +=" left join cities c01 on(c01.id = t.id_city)";                
        sql +=" where "+ condicoes.substring(0,condicoes.length()-3) +" order by t.go_time asc";

        try {
            ResultSet rs = db.executeResultSet(sql);

            while(rs.next()){
                RouteTime time = new RouteTime();
                time.setTime(Time.valueOf(rs.getString("go_time")));
                City c = new City();
                c.setId(rs.getInt("time_city"));                
                time.setCity(c);
                RouteTravel rtv = new RouteTravel();
                rtv.setId(rs.getInt("travel"));
                rtv.setType(rs.getInt("id_type"));
                rtv.setRoute(getRoute(lst,rs.getInt("route_id")));
                rtv.getRoute().getTravels().add(rtv);
                time.setRouteTravel(rtv);
                times.add(time);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Search.class.getName()).log(Level.SEVERE, null, ex);
        }
        times = fillArrivalTime(times);
        return fillPrice(times,db);
    }

    private ArrayList<RouteTime> fillPrice(ArrayList<RouteTime> lst, Db db){
         String condicoes = "";
        for(RouteTime rt:lst){
            condicoes += "(r.id = "+rt.getRouteTravel().getRoute().getId() + " and rp.id_city_from="+cityFrom.getId()+" and rp.id_city_to = "+cityTo.getId()+") or";
        }
        String sql = "select r.id as route, rp.id_city_to as city_from, rp.id_city_to as city_to,rp.price from routes_prices rp";
        sql +=" left join routes r on(r.id = rp.id_route)";
        sql +=" where "+ condicoes.substring(0,condicoes.length()-3) +" order by r.id asc";

        ArrayList<RoutePrice> prices = new ArrayList<RoutePrice>();
        try {
            ResultSet rs = db.executeResultSet(sql);

            while(rs.next()){
                RoutePrice price = new RoutePrice();
                price.setPrice(rs.getDouble("price"));
                Route r = new Route();
                r.setId(rs.getInt("route"));
                price.setRoute(r);
                prices.add(price);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Search.class.getName()).log(Level.SEVERE, null, ex);
        }
        for(RouteTime rt: lst){
            for(RoutePrice rp: prices){
                if(rp.getRoute().getId() != rt.getRouteTravel().getRoute().getId())
                    continue;
                rt.setPrice(rp.getPrice());
                break;
            }
        }
        return lst;
    }

    private ArrayList<RouteTime> fillArrivalTime(ArrayList<RouteTime> lst){
        ArrayList<RouteTime> goes = new ArrayList<RouteTime>();
        ArrayList<RouteTime> arrivals = new ArrayList<RouteTime>();

        for(RouteTime rt:lst){
            if(rt.getCity().getId() == cityFrom.getId()){
                goes.add(rt);
            }
            else if(rt.getCity().getId() == cityTo.getId()){
                arrivals.add(rt);
            }
        }
        for(RouteTime rt:goes){
            int i =0;
            int j = arrivals.size();
            for(i=0;i<j;i++){
                RouteTime arrival = arrivals.get(i);
                if(rt.getRouteTravel().getId() == arrival.getRouteTravel().getId()){
                    rt.setArrival(arrival.getTime());
                    arrivals.remove(i);
                    j--;
                    i--;
                }
            }
        }
        return goes;
    }
    
    Route getRoute(ArrayList<Route> lst, int id){
        for(Route r: lst){
            if(r.getId()== id)
                return r;
        }
        return null;
    }
 
}
